﻿IF ( EXISTS( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CaseEvents' ) )
BEGIN
	IF ( NOT EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'CreatedBy' AND TABLE_NAME = 'CaseEvents' ) )
	BEGIN
		ALTER TABLE [dbo].[CaseEvents] ADD CreatedBy UNIQUEIDENTIFIER NOT NULL
		PRINT 'Added CaseEvents.CreatedBy column.'
	END
	ELSE
	BEGIN
		PRINT 'CaseEvents.CreatedBy already exists.'
	END
	
	IF ( NOT EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'CreatedOn' AND TABLE_NAME = 'CaseEvents' ) )
	BEGIN
		ALTER TABLE [dbo].[CaseEvents] ADD CreatedOn DATETIME NOT NULL CONSTRAINT [DFLT_CaseEvents_CreatedOn] DEFAULT ( GetDate() )
		PRINT 'Added CaseEvents.CreatedOn column.'
	END
	ELSE
	BEGIN
		PRINT 'CaseEvents.CreatedOn already exists.'
	END
	
	IF ( NOT EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'CompletedBy' AND TABLE_NAME = 'CaseEvents' ) )
	BEGIN
		ALTER TABLE [dbo].[CaseEvents] ADD CompletedBy UNIQUEIDENTIFIER NULL
		PRINT 'Added CaseEvents.CompletedBy column.'
	END
	ELSE
	BEGIN
		PRINT 'CaseEvents.CompletedBy already exists.'
	END
	
	IF ( NOT EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'CompletedOn' AND TABLE_NAME = 'CaseEvents' ) )
	BEGIN
		ALTER TABLE [dbo].[CaseEvents] ADD CompletedOn DATETIME NULL
		PRINT 'Added CaseEvents.CompletedOn column.'
	END
	ELSE
	BEGIN
		PRINT 'CaseEvents.CompletedOn already exists.'
	END
	
	IF ( EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'UserID' AND TABLE_NAME = 'CaseEvents' ) )
	BEGIN
		ALTER TABLE [dbo].[CaseEvents] DROP CONSTRAINT FK_CaseEvents_Users
		ALTER TABLE [dbo].[CaseEvents] DROP COLUMN UserID
		PRINT 'Dropped CaseEvents.UserID column.'
	END
	ELSE
	BEGIN
		PRINT 'CaseEvents.UserID already dropped.'
	END
	
	IF ( EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'EventOccurred' AND TABLE_NAME = 'CaseEvents' ) )
	BEGIN
		ALTER TABLE [dbo].[CaseEvents] DROP CONSTRAINT DFLT_CaseEvents_EventTime
		ALTER TABLE [dbo].[CaseEvents] DROP COLUMN EventOccurred
		PRINT 'Dropped CaseEvents.EventOccurred column.'
	END
	ELSE
	BEGIN
		PRINT 'CaseEvents.EventOccurred already dropped.'
	END
	
END
ELSE
BEGIN
	PRINT 'CaseEvents table does not exist'
END
GO
